﻿CREATE PROCEDURE dbo.SP_SecureMemberDTO_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL

, @MemberId Int   =  NULL 


, @FirstName NVarChar (256)  =  NULL 


, @LastName NVarChar (256)  =  NULL 


, @DisplayName NVarChar (256)  =  NULL 


, @Email NVarChar (256)  =  NULL 


, @Gender TinyInt   =  NULL 


, @Birthdate DateTime   =  NULL 


, @NationalityId Int   =  NULL 


, @CountryId Int   =  NULL 


, @CreationDate DateTime   =  NULL 


, @IsActive Bit   =  NULL 


, @LastUpdateDate DateTime   =  NULL 


, @ImagePath NVarChar (50)  =  NULL 


, @ConfessionFather NChar (10)  =  NULL 

	AS
			WITH CTE_SecureMember AS
			( 
			SELECT
 							v.MemberId,
								v.FirstName,
								v.LastName,
								v.DisplayName,
								v.Email,
								v.Gender,
								v.Birthdate,
								v.NationalityId,
								v.CountryId,
								v.CreationDate,
								v.IsActive,
								v.LastUpdateDate,
								v.ImagePath,
								v.ConfessionFather,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume='MemberId' THEN v.MemberId
						 
						WHEN @OrderColume='FirstName' THEN v.FirstName
						 
						WHEN @OrderColume='LastName' THEN v.LastName
						 
						WHEN @OrderColume='DisplayName' THEN v.DisplayName
						 
						WHEN @OrderColume='Email' THEN v.Email
												WHEN @OrderColume='Gender' THEN v.Gender
												WHEN @OrderColume='Birthdate' THEN v.Birthdate
												WHEN @OrderColume='NationalityId' THEN v.NationalityId
												WHEN @OrderColume='CountryId' THEN v.CountryId
												WHEN @OrderColume='CreationDate' THEN v.CreationDate
												WHEN @OrderColume='IsActive' THEN v.IsActive
												WHEN @OrderColume='LastUpdateDate' THEN v.LastUpdateDate
						 
						WHEN @OrderColume='ImagePath' THEN v.ImagePath
						 
						WHEN @OrderColume='ConfessionFather' THEN v.ConfessionFather
						        	END
			) AS "RowNumber"
  			FROM SecureMember v 
			where 
								(MemberId=@MemberId OR @MemberId Is Null)
										AND(FirstName=@FirstName OR @FirstName Is Null)
										AND(LastName=@LastName OR @LastName Is Null)
										AND(DisplayName=@DisplayName OR @DisplayName Is Null)
										AND(Email=@Email OR @Email Is Null)
										AND(Gender=@Gender OR @Gender Is Null)
										AND(Birthdate=@Birthdate OR @Birthdate Is Null)
										AND(NationalityId=@NationalityId OR @NationalityId Is Null)
										AND(CountryId=@CountryId OR @CountryId Is Null)
										AND(CreationDate=@CreationDate OR @CreationDate Is Null)
										AND(IsActive=@IsActive OR @IsActive Is Null)
										AND(LastUpdateDate=@LastUpdateDate OR @LastUpdateDate Is Null)
										AND(ImagePath=@ImagePath OR @ImagePath Is Null)
										AND(ConfessionFather=@ConfessionFather OR @ConfessionFather Is Null)
								)
			SELECT *
			FROM CTE_SecureMember
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (
			SELECT Count(*) AS TotalRecords 
            FROM SecureMember v
            where 
								(v.MemberId=@MemberId OR @MemberId Is Null)
										AND(v.FirstName=@FirstName OR @FirstName Is Null)
										AND(v.LastName=@LastName OR @LastName Is Null)
										AND(v.DisplayName=@DisplayName OR @DisplayName Is Null)
										AND(v.Email=@Email OR @Email Is Null)
										AND(v.Gender=@Gender OR @Gender Is Null)
										AND(v.Birthdate=@Birthdate OR @Birthdate Is Null)
										AND(v.NationalityId=@NationalityId OR @NationalityId Is Null)
										AND(v.CountryId=@CountryId OR @CountryId Is Null)
										AND(v.CreationDate=@CreationDate OR @CreationDate Is Null)
										AND(v.IsActive=@IsActive OR @IsActive Is Null)
										AND(v.LastUpdateDate=@LastUpdateDate OR @LastUpdateDate Is Null)
										AND(v.ImagePath=@ImagePath OR @ImagePath Is Null)
										AND(v.ConfessionFather=@ConfessionFather OR @ConfessionFather Is Null)
					 	
			)